package com.elitel.frame.business.service.impl;

import java.util.List;
import java.util.Map;

import com.alibaba.fastjson.JSON;
import com.elitel.common.messageQueue.RabbitMQHelper;
import com.elitel.frame.base.component.RedisCacheExecuteSql;
import com.elitel.frame.business.dao.ext.ExecuteSqlMapper;
import com.elitel.frame.business.dao.ext.SingleTableServerMapper;
import com.elitel.frame.business.service.ExecuteSqlService;
import com.elitel.frame.main.entity.CfgDb;
import com.elitel.frame.main.entity.CfgExtsql;
import com.elitel.frame.main.entity.vo.ConQueryVo;
import com.elitel.frame.main.entity.vo.PageResponseVo;
import com.elitel.frame.main.entity.vo.ServerStatistics;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class ExecuteSqlServiceImpl implements ExecuteSqlService{
	
	@Autowired
	private ExecuteSqlMapper executeSqlMapper;
	@Autowired
	private SingleTableServerMapper singleTableServerMapper;
	@Autowired
	private RabbitMQHelper rabbitMQHelper;
	@Autowired
	private RedisCacheExecuteSql redisCacheExecuteSql;
	
	@Override
	public PageResponseVo<Map<String, Object>> executequerySqlPagination(CfgDb cfgDB, String serviceID, String pageNO, String pageSize, String conQuery, CfgExtsql cfgExtsql) {

		PageResponseVo<Map<String, Object>> pageResponse = new PageResponseVo<Map<String, Object>>();
		if(cfgDB == null || cfgExtsql == null)
			return pageResponse;
		ServerStatistics serverRecorder = new ServerStatistics();
		serverRecorder.startRecord();

		List<Map<String, Object>> list = null;
		//条件的集合
		List<ConQueryVo> pays = null;
		Long count = null;
		//结束条数和起始条数
		Integer endSize = Integer.valueOf(pageNO) * Integer.valueOf(pageSize);
		Integer startSize = (Integer.valueOf(pageNO) - 1) * Integer.valueOf(pageSize);
		if(null != conQuery && !conQuery.equals("")) {
			pays =(List<ConQueryVo>) JSON.parseArray(conQuery, ConQueryVo.class);
		}
		String sql = "";
		String count_sql = "";
		String whereSql = "";

		switch (cfgDB.getDbVersion()) {
				//查询总条数 sql
				//条件查询语句 sql
			case "ORACLE":
				if(null != pays && pays.size() > 0 ) {
					whereSql = loadWhereSql(cfgDB.getDbVersion(), pays);
					count_sql = "select count(*) from (" + cfgExtsql.getDtSql() + " where " + whereSql + ")";
					sql = "SELECT * FROM (  SELECT A.*, ROWNUM RN  FROM (SELECT * FROM (" + cfgExtsql.getDtSql() + " where " + whereSql + ") ) A WHERE ROWNUM <= " + endSize + " )  WHERE RN > " + startSize;
				} else {
					count_sql = "select count(*) from (" + cfgExtsql.getDtSql() + ")";
					sql = "SELECT * FROM (  SELECT A.*, ROWNUM RN  FROM (SELECT * FROM (" + cfgExtsql.getDtSql() + ") ) A WHERE ROWNUM <= " + endSize + " )  WHERE RN > " + startSize;
				}
				break;

			case "MYSQL":
				if(null != pays && pays.size() > 0 ) {
					whereSql = loadWhereSql(cfgDB.getDbVersion(), pays);
					count_sql = "select count(*) from (" + cfgExtsql.getDtSql() + " where " + whereSql + ")";
					sql = "SELECT * FROM (" + cfgExtsql.getDtSql() + " where " + whereSql + ") LIMIT " + startSize + "," + pageSize;
				} else {
					count_sql = "select count(*) from (" + cfgExtsql.getDtSql() + ")";
					sql = "SELECT * FROM (" + cfgExtsql.getDtSql() + ") LIMIT " + startSize + "," + pageSize;
				}
				break;
			case "SQLSERVER":
				if(null != pays && pays.size() > 0 ) {
					whereSql = loadWhereSql(cfgDB.getDbVersion(), pays);
					count_sql = "select count(*) from (" + cfgExtsql.getDtSql() + " where " + whereSql + ")";
					sql = "select * from(select ROW_NUMBER() OVER (ORDER BY GETDATE() ) RN, *  from (" + cfgExtsql.getDtSql() + ") c" + whereSql + ")a WHERE RN Between " + (startSize+1) + " and " + endSize;
					count = singleTableServerMapper.singleTBCount(count_sql);
				} else {
					count_sql = "select count(*) from (" + cfgExtsql.getDtSql() + ")";
					sql = "select * from(select ROW_NUMBER() OVER (ORDER BY GETDATE() ) RN, *  from (" + cfgExtsql.getDtSql() + ") )a WHERE RN Between " + (startSize+1) + " and " + endSize;
				}
				break;
		}

		serverRecorder.recordSqlStartTime();
		//查询总条数
		count = singleTableServerMapper.singleTBCount(count_sql);
		serverRecorder.recordSqlEndTime();

		//条件查询语句
		list = executeSqlMapper.executequerySql(sql);
		serverRecorder.recordServerEndTime();


		serverRecorder.setServerName(cfgExtsql.getServicename());
		serverRecorder.setSql(sql);
		rabbitMQHelper.recordTimeConsumeIfNecessary(serverRecorder);

		pageResponse.setTotal(count.intValue());
		pageResponse.setRows(list);

		return pageResponse;
	}

	@Override
	public PageResponseVo<Map<String, Object>> executequerySql(CfgDb cfgDB, String serviceID, String conQuery, CfgExtsql cfgExtsql) {

		PageResponseVo<Map<String, Object>> pageResponse = new PageResponseVo<Map<String, Object>>();
		if(cfgExtsql == null)
			return pageResponse;

		ServerStatistics serverRecorder = new ServerStatistics();
		serverRecorder.startRecord();

		List<Map<String, Object>> queryResultList = null;

		//条件的集合
		List<ConQueryVo> pays = null;
		Long queryResultCount = null;
		if(null != conQuery && !conQuery.equals("")) {
			pays =(List<ConQueryVo>) JSON.parseArray(conQuery, ConQueryVo.class);
		}
		String sql = "";
		String whereSql = "";
		String count_sql = "";

		if(null != pays && pays.size() > 0 ) {
			whereSql = loadWhereSql(cfgDB.getDbVersion(), pays);
			count_sql = "select count(*) from (" + cfgExtsql.getDtSql() + " where " + whereSql + ")";
			sql = "SELECT * FROM (" + cfgExtsql.getDtSql() + " where " + whereSql + ")";

		} else if (null != cfgExtsql.getSyscatch() && cfgExtsql.getSyscatch() == 1){
			count_sql = "select count(*) from (" + cfgExtsql.getDtSql() + ")";
			sql = "SELECT * FROM (" + cfgExtsql.getDtSql() + ")";
			pageResponse = redisCacheExecuteSql.executeSqlServerList(cfgExtsql, sql, count_sql);
			return pageResponse;

		} else if (null != cfgExtsql.getSyscatch() && cfgExtsql.getSyscatch() == 0){
			count_sql = "select count(*) from (" + cfgExtsql.getDtSql()  + ")";
			sql = "SELECT * FROM (" + cfgExtsql.getDtSql()  + ")";
		}

		serverRecorder.recordSqlStartTime();
		//查询总条数
		queryResultCount = singleTableServerMapper.singleTBCount(count_sql);
		serverRecorder.recordSqlEndTime();

		//条件查询语句
		queryResultList = executeSqlMapper.executequerySql(sql);
		serverRecorder.recordServerEndTime();


		serverRecorder.setServerName(cfgExtsql.getServicename());
		serverRecorder.setSql(sql);
		rabbitMQHelper.recordTimeConsumeIfNecessary(serverRecorder);

		pageResponse.setTotal(queryResultCount.intValue());
		pageResponse.setRows(queryResultList);

		return pageResponse;
	}
	
	
	@Override
	public List<Map<String, Object>> executeSql(CfgDb cfgDB, String sql) {
		List<Map<String, Object>> executequerySql = executeSqlMapper.executequerySql(sql);
		return executequerySql;
	}
	/**
	 * 
	 * @Title: loadWhereSql 
	 * @Description: TODO(根据不同的数据库类型返回不同的条件语句) 
	 * @author Fanwenquan
	 * @return String
	 * @throws
	 */
	private String loadWhereSql(String dbType, List<ConQueryVo> list){
		StringBuffer str = new StringBuffer("");
		switch (dbType) {
		case "ORACLE":
			for (int i = 0; i < list.size(); i++) {
				if(i != 0) {
					str.append(" and ");
				}
				switch (list.get(i).getType()) {
				case "int":
					str.append(" "+list.get(i).getField());
					str.append(list.get(i).getCon());
					str.append(list.get(i).getValue());
					break;
				case "string":
					if (list.get(i).getCon().equals("like")) {
						str.append(" "+list.get(i).getField());
						str.append(" "+list.get(i).getCon()+" ");
						str.append("'%" + list.get(i).getValue() + "%'");
					} else {
						str.append(" "+list.get(i).getField());
						str.append(list.get(i).getCon());
						str.append("'" + list.get(i).getValue() + "'");
					}
					break;
				case "year":
					str.append(" "+list.get(i).getField());
					str.append(list.get(i).getCon());
					str.append(" to_date('" + list.get(i).getValue() + "','yyyy')");
					break;
				case "month":
					str.append(" to_CHAR("+list.get(i).getField() + ",'MM')");
					str.append(list.get(i).getCon());
					str.append("'" + list.get(i).getValue() + "'");
					break;
				case "day":
					str.append(" to_CHAR("+list.get(i).getField() + ",'dd')");
					str.append(list.get(i).getCon());
					str.append("'" + list.get(i).getValue() + "'");
					break;
				case "ymonth":
					str.append(" "+list.get(i).getField());
					str.append(list.get(i).getCon());
					str.append(" to_date('" + list.get(i).getValue() + "','yyyy-MM')");
					break;
				case "date":
					str.append(" "+list.get(i).getField());
					str.append(list.get(i).getCon());
					str.append(" to_date('" + list.get(i).getValue() + "','yyyy-MM-dd')");
					break;
				case "datehour":
					str.append(" "+list.get(i).getField());
					str.append(list.get(i).getCon());
					str.append(" to_date('" + list.get(i).getValue() + "','yyyy-MM-dd hh24')");
					break;
				case "datehmin":
					str.append(" "+list.get(i).getField());
					str.append(list.get(i).getCon());
					str.append(" to_date('" + list.get(i).getValue() + "','yyyy-MM-dd hh24:mi')");
					break;
				case "datetime":
					str.append(" "+list.get(i).getField());
					str.append(list.get(i).getCon());
					str.append(" to_date('" + list.get(i).getValue() + "','yyyy-MM-dd hh24:mi:ss')");
					break;
				}
				
			}
			break;

		case "MYSQL":
			for (int i = 0; i < list.size(); i++) {
				if(i != 0) {
					str.append(" and ");
				}
				switch (list.get(i).getType()) {
				case "int":
					str.append(" "+list.get(i).getField());
					str.append(list.get(i).getCon());
					str.append(list.get(i).getValue());
					break;
				case "string":
					if (list.get(i).getCon().equals("like")) {
						str.append(" "+list.get(i).getField());
						str.append(" "+list.get(i).getCon()+" ");
						str.append("'%" + list.get(i).getValue() + "%'");
					} else {
						str.append(" "+list.get(i).getField());
						str.append(list.get(i).getCon());
						str.append("'" + list.get(i).getValue() + "'");
					}
					break;
				case "year":
					str.append(" year("+list.get(i).getField() + ")");
					str.append(list.get(i).getCon());
					str.append("'" + list.get(i).getValue() + "'");
					break;
				case "month":
					str.append(" month("+list.get(i).getField() + ")");
					str.append(list.get(i).getCon());
					str.append("'" + list.get(i).getValue() + "'");
					break;
				case "day":
					str.append(" DAY("+list.get(i).getField() + ")");
					str.append(list.get(i).getCon());
					str.append("'" + list.get(i).getValue() + "'");
					break;
				case "ymonth":
					str.append(" "+list.get(i).getField());
					str.append(list.get(i).getCon());
					str.append(" str_to_date('" + list.get(i).getValue() + "','%Y-%m')");
					break;
				case "date":
					str.append(" "+list.get(i).getField());
					str.append(list.get(i).getCon());
					str.append(" str_to_date('" + list.get(i).getValue() + "','%Y-%m-%d')");
					break;
				case "datehour":
					str.append(" "+list.get(i).getField());
					str.append(list.get(i).getCon());
					str.append(" str_to_date('" + list.get(i).getValue() + "','%Y-%m-%d %H')");
					break;
				case "datehmin":
					str.append(" "+list.get(i).getField());
					str.append(list.get(i).getCon());
					str.append(" str_to_date('" + list.get(i).getValue() + "','%Y-%m-%d %H:%i')");
					break;
				case "datetime":
					str.append(" "+list.get(i).getField());
					str.append(list.get(i).getCon());
					str.append(" str_to_date('" + list.get(i).getValue() + "','%Y-%m-%d %H:%i:%S')");
					break;
				}
				
			}
			break;
		case "SQLSERVER":
			for (int i = 0; i < list.size(); i++) {
				if(i != 0) {
					str.append(" and ");
				}
				switch (list.get(i).getType()) {
				case "int":
					str.append(" "+list.get(i).getField());
					str.append(list.get(i).getCon());
					str.append(list.get(i).getValue());
					break;
				case "string":
					if (list.get(i).getCon().equals("like")) {
						str.append(" "+list.get(i).getField());
						str.append(" "+list.get(i).getCon()+" ");
						str.append("'%" + list.get(i).getValue() + "%'");
					} else {
						str.append(" "+list.get(i).getField());
						str.append(list.get(i).getCon());
						str.append("'" + list.get(i).getValue() + "'");
					}
					break;
				case "year":
					str.append(" year("+list.get(i).getField() + ")");
					str.append(list.get(i).getCon());
					str.append(list.get(i).getValue());
					break;
				case "month":
					str.append(" month("+list.get(i).getField() + ")");
					str.append(list.get(i).getCon());
					str.append(list.get(i).getValue());
					break;
				case "day":
					str.append(" day("+list.get(i).getField() + ")");
					str.append(list.get(i).getCon());
					str.append(list.get(i).getValue());
					break;
				case "ymonth":
					str.append(" "+list.get(i).getField());
					str.append(list.get(i).getCon());
					str.append(list.get(i).getValue());
					break;
				case "date":
					str.append(" "+list.get(i).getField());
					str.append(list.get(i).getCon());
					str.append(list.get(i).getValue());
					break;
				case "datehour":
					str.append(" "+list.get(i).getField());
					str.append(list.get(i).getCon());
					str.append(list.get(i).getValue());
					break;
				case "datehmin":
					str.append(" "+list.get(i).getField());
					str.append(list.get(i).getCon());
					str.append(list.get(i).getValue());
					break;
				case "datetime":
					str.append(" "+list.get(i).getField());
					str.append(list.get(i).getCon());
					str.append(list.get(i).getValue());
					break;
				}
				
			}
			break;
		}
		return str.toString();
	}

}
